const express = require('express')
const bodyParser = require('body-parser')
const { format } = require('date-fns')
const moment = require('moment')
const cors = require('cors')
const mysql = require('mysql2')
const jwt = require('jsonwebtoken')
const app = express()

app.use(bodyParser.json())
app.use(cors())
app.use(bodyParser.urlencoded({ extended: true }))

// 设置统一的JSON响应格式
function createResponse(code, msg, data) {
  return {
    code,
    msg,
    data
  }
}

// 密钥，用于签名token
const secretKey = 'cocoxuan'
// 结合MySQL数据库
const connection = mysql.createPool({
  host: '120.55.195.159',
  user: 'root',
  password: 'cocoX@123',
  database: 'headlines',
  waitForConnections: true,
  connectionLimit: 100, // 最大连接数
  queueLimit: 0, // 无限制排队数
  keepAlive: true, // 开启连接保持活跃功能
  keepAliveInitialDelay: 30000 // 30秒发送一次保持活跃的请求
})

connection.getConnection(err => {
  if (err) {
    console.error('Error connecting to MySQL database: ' + err.stack)
    return
  }
})

// 中间件函数：验证令牌
function verifyToken(req, res, next) {
  const authHeader = req.headers['authorization'];
  const token = authHeader && authHeader.split(' ')[1]
  if (!token) {
    return res.send(createResponse(401, '请登录'))
  }
  jwt.verify(token, 'secret', (err, decoded) => {
    if (err) {
      return res.send(createResponse(401, '身份失效，请重新登录'))
      // return res.status(500).json(createResponse(401, '身份失效，请重新登录'));
    }
    // console.log(loggedInUsers, '这里检查不对么');
    // 检查令牌中的手机号是否已经有一个有效的登录会话存在
    // if (!loggedInUsers[decoded.phone] || loggedInUsers[decoded.phone] !== token) {
    //   return res.send(createResponse(401, '身份失效，请重新登录'))
    // }
    // console.log(decoded, '这里是身份信息');
    // 令牌验证通过，将手机号存储到请求对象中，以便后续路由使用
    req.phone = decoded.phone;
    req.userId = decoded.userId
    next();
  });
}
// 用于存储已登录用户信息的对象
const loggedInUsers = {};
app.use((req, res, next) => {
  res.set('Cache-Control', 'no-store');
  next();
});
// 登录接口
app.post('/login', (req, res) => {
  // console.log('被请求了', req);
  const { phone, password } = req.body;
  connection.query('SELECT * FROM user WHERE phone = ? AND password = ?', [phone, password], (err, results) => {
    if (err) {
      console.log(err);
      return res.status(500).send(createResponse(500, 'Internal Server Error'))
    }
    if (results.length > 0) {
      // 检查该手机号是否已经有一个有效的登录会话存在
      if (loggedInUsers[phone]) {
        // 使先前的会话失效
        delete loggedInUsers[phone];
      }
      let userId = results[0].id
      // 创建一个 JWT 令牌
      const token = jwt.sign({ phone, userId }, 'secret', { expiresIn: '1h' });
      // 存储该用户的登录信息
      loggedInUsers[phone] = token;
      return res.send(createResponse(200, '登录成功', { token }))

    } else {
      connection.query('SELECT * FROM user WHERE phone = ?', [phone], (err, results) => {
        if (results.length > 0) {
          return res.status(401).json(createResponse(401, '密码不正确'))
        } else {
          return res.status(401).json(createResponse(401, '用户不存在'))
        }
      })
    }
  })
});

// 保护的路由，需要验证令牌
app.get('/protected', verifyToken, (req, res) => {

  return res.send(createResponse(200, '登录成功', { userId: req.userId }))
  // connection.query('SELECT * FROM user',, (err, results) => { })

});
// 获取d首页全部数据
app.get('/data', verifyToken, (req, res) => {
  let { userId } = req.query

  let sql = 'SELECT * FROM data WHERE data.user_id =  ?'
  connection.query(sql, [userId], (err, results) => {
    if (err) {
      // 如果有错误，返回错误信息
      return res.status(500).send(createResponse(500, 'Internal Server Error'))
    }
    // console.log(results[0], '这里不行么');
    return res.status(200).json(createResponse(200, '', results[0]))
  })
});
// 获取d首页全部数据
app.get('/data/list', verifyToken, (req, res) => {
  let { userId } = req.query

  let sql = 'SELECT * FROM data WHERE data.user_id =  ?'
  connection.query(sql, [userId], (err, results) => {
    if (err) {
      // 如果有错误，返回错误信息
      return res.status(500).send(createResponse(500, 'Internal Server Error'))
    }

    return res.status(200).json(createResponse(200, '', results))
  })
});
// 添加或更新用户数据
app.post('/data/update', verifyToken, (req, res) => {
  const { user_id, all_fans, all_money, all_read_num, avatar, fans_add, money_add, read_add, money, day } = req.body;

  // 检查数据库中是否已经存在该用户的数据
  connection.query('SELECT * FROM data WHERE user_id = ?', [user_id], (err, results) => {
    if (err) {
      return res.status(500).json({ error: 'Database error' });
    }
    if (results.length > 0) {
      // 如果已存在该用户数据，则执行更新操作
      connection.query('UPDATE data SET all_fans=?, all_money=?, all_read_num=?, avatar=?, fans_add=?, money_add=?, read_add=?, money=?, day=? WHERE user_id=?',
        [all_fans, all_money, all_read_num, avatar, fans_add, money_add, read_add, money, day, user_id],
        (err, results) => {
          if (err) {
            return res.status(500).json({ error: 'Error updating data' });
          }
          return res.status(200).json(createResponse(200, 'User data updated successfully'));
        });
    } else {
      // 如果不存在该用户数据，则执行插入操作
      connection.query('INSERT INTO data (user_id, all_fans, all_money, all_read_num, avatar, fans_add, money_add, read_add, money) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)',
        [user_id, all_fans, all_money, all_read_num, avatar, fans_add, money_add, read_add, day, money],
        (err, results) => {
          if (err) {
            return res.status(500).json({ error: 'Error inserting data' });
          }
          return res.status(200).json(createResponse(200, 'User data inserted successfully'));
        });
    }
  });
});
// 获取收益记录
app.get('/income', verifyToken, (req, res) => {
  const { num, userId: user_id } = req.query;
  // 构建日期范围
  const today = new Date();
  today.setHours(0, 0, 0, 0);  // 将时间设置为当天的开始
  const endDate = today.toISOString().slice(0, 10);  // 今日的日期
  const startDate = new Date(today);
  startDate.setDate(startDate.getDate() - num);
  const startDateStr = startDate.toISOString().slice(0, 10);  // num 天前的日期
  // 查询数据库获取收入数据
  const query = `
    SELECT income_date, \`all\`, work_money
    FROM income
    WHERE user_id = ? AND income_date >= ? AND income_date <= ?
  `;
  connection.query(query, [user_id, startDateStr, endDate], (error, results, fields) => {
    if (error) {
      console.error('Error querying database: ' + error);
      res.status(500).send('Error querying database');
    } else {
      // console.log(results, '这里没查出来么');
      const data = results.map(row => ({
        income_date: convertUTCToLocalDate(row.income_date),
        all: row.all,
        work_money: row.work_money
      }));
      // console.log(data, '这里是转化后的');
      // 创建虚拟数据补充缺失日期
      // console.log(startDateStr, today);
      const missingDates = getMissingDates(new Date(startDateStr), today, data);
      // console.log(missingDates, '这里出来问题');


      let list = [...data]
      list = list.map(item => ({
        income_date: moment(item.income_date).format('YYYY-MM-DD'),
        all: item.all,
        work_money: item.work_money
      }))
      // console.log(list, '这里是数据库的', missingDates);
      missingDates.forEach(item => {
        let num = list.findIndex(k => {
          return k.income_date === item.income_date
        })
        if (num === -1) {
          list.push(item)
        }
      })
      list.sort((a, b) => {
        return new Date(b.income_date) - new Date(a.income_date);
      });
      return res.status(200).json(createResponse(200, '', list))
    }
  });
});
function convertUTCToLocalDate(dateStr) {
  // 创建一个表示给定日期的 Date 对象
  let date = new Date(dateStr);

  // 获取 UTC 时间偏移量（以分钟为单位）
  let utcOffset = date.getTimezoneOffset();

  // 将 UTC 时间偏移量转换为毫秒数
  let utcOffsetMs = utcOffset * 60 * 1000;

  // 获取 UTC 时间的时间戳
  let utcTimestamp = date.getTime();

  // 计算中国时区的时间戳（加上 UTC 时间偏移量）
  let chinaTimestamp = utcTimestamp + utcOffsetMs + (8 * 60 * 60 * 1000);

  // 创建表示中国时区的本地日期对象
  let chinaDate = new Date(chinaTimestamp);

  // 格式化为字符串并返回
  return chinaDate.toISOString();
}

// 辅助函数：创建缺失日期的虚拟数据
function getMissingDates(startDate, endDate, existingData) {
  const missingDates = [];
  let currentDate = new Date(startDate);
  while (currentDate <= endDate) {
    const dateStr = currentDate.toISOString().slice(0, 10);
    if (!existingData.some(item => item.income_date === dateStr)) {
      let randomNumber = Math.floor(Math.random() * 30001 + 10000) / 100;
      if (randomNumber % 1 === 0) {
        randomNumber += 0.01; // 如果生成的随机数为整数，则加上 0.01，确保不包括整数部分
      }
      randomNumber = randomNumber.toFixed(2); // 确保小数部分有两位数
      missingDates.push({
        income_date: dateStr,
        all: randomNumber,
        work_money: randomNumber
      });
    }
    currentDate.setDate(currentDate.getDate() + 1);
  }
  return missingDates;
}
// 获取d首页全部数据
app.get('/income/list', verifyToken, (req, res) => {
  let { userId } = req.query

  let sql = 'SELECT * FROM income WHERE income.user_id =  ?'
  connection.query(sql, [userId], (err, results) => {
    if (err) {
      // console.log(err);
      // 如果有错误，返回错误信息
      return res.status(500).send(createResponse(500, 'Internal Server Error'))
    }

    return res.status(200).json(createResponse(200, '', results))
  })
});

app.post('/income', (req, res) => {
  let { id, income_date, all, work_money, user_id } = req.body;
  const date = new Date(income_date);
  const utcDate = new Date(Date.UTC(date.getFullYear(), date.getMonth(), date.getDate()));
  income_date = utcDate.toISOString().split('T')[0];
  // console.log(income_date, '这里是日期');
  // 检查是否提供了 id
  if (id) {
    // 如果提供了 id，则为修改记录操作
    // 首先检查是否存在与修改后的 income_date 相同的记录（排除当前记录本身）
    const checkDuplicateQuery = `
      SELECT COUNT(*) AS count
      FROM income
      WHERE income_date = ? AND id != ? AND user_id = ?
    `;
    connection.query(checkDuplicateQuery, [income_date, id, user_id], (error, results, fields) => {
      if (error) {
        console.error('Error checking duplicate income_date: ' + error);
        res.status(500).send('Error checking duplicate income_date');
        return;
      }
      const count = results[0].count;

      if (count > 0) {

        // 如果存在与修改后的 income_date 相同的记录，返回错误消息
        return res.send(createResponse(500, '该日期已有数据'))
      } else {
        // 如果不存在与修改后的 income_date 相同的记录，执行修改操作
        const updateQuery = `
        UPDATE income
        SET income_date = ?, \`all\` = ?, work_money = ?, user_id = ?
        WHERE id = ?
      `;
        connection.query(updateQuery, [income_date, all, work_money, user_id, id], (error, results, fields) => {
          if (error) {
            console.error('Error updating income record: ' + error);
            res.status(500).send('Error updating income record');
          } else {
            res.status(200).send('Income record updated successfully');
          }
        });
      }
    });
  } else {
    // 如果没有提供 id，则为新增记录操作
    // 首先检查是否存在相同日期的记录
    const checkDuplicateQuery = `
      SELECT COUNT(*) AS count
      FROM income
      WHERE income_date = ? AND user_id = ?
    `;
    connection.query(checkDuplicateQuery, [income_date, user_id], (error, results, fields) => {
      if (error) {
        console.error('Error checking duplicate income_date: ' + error);
        res.status(500).send('Error checking duplicate income_date');
        return;
      }

      const count = results[0].count;
      if (count > 0) {
        // 如果存在相同日期的记录，返回错误消息
        return res.send(createResponse(500, '该日期已有数据'))
      } else {
        // 如果不存在相同日期的记录，执行插入操作
        const insertQuery = `
          INSERT INTO income (income_date, \`all\`, work_money, user_id)
          VALUES (?, ?, ?, ?)
        `;
        connection.query(insertQuery, [income_date, all, work_money, user_id], (error, results, fields) => {
          if (error) {
            console.error('Error inserting new income record: ' + error);
            res.status(500).send('Error inserting new income record');
          } else {
            res.status(200).send('New income record inserted successfully');
          }
        });
      }
    });
  }
});

// 删除
app.delete('/income/:id', verifyToken, (req, res) => {
  const id = req.params.id;
  // 执行删除操作
  const deleteQuery = `
    DELETE FROM income
    WHERE id = ?
  `;
  connection.query(deleteQuery, [id], (error, results, fields) => {
    if (error) {
      console.error('Error deleting work record: ' + error);
      res.status(500).send('Error deleting work record');
    } else {
      res.status(200).send('Work record deleted successfully');
    }
  });
});



// 查询银行储蓄
// 获取d首页全部数据
app.get('/bank/list', verifyToken, (req, res) => {
  let { userId } = req.query
  // console.log(userId, '这里能取到么');
  let sql = 'SELECT * FROM bank WHERE bank.user_id = ? ORDER BY bank_time DESC'
  connection.query(sql, [userId], (err, results) => {
    if (err) {
      // 如果有错误，返回错误信息
      return res.status(500).send(createResponse(500, 'Internal Server Error'))
    }
    // if (results.length > 0) {
    //   results.sort((a, b) => new Date(a.bank_time) - new Date(b.bank_time));
    // }
    return res.status(200).json(createResponse(200, '', results))
  })
});

app.post('/bank', verifyToken, (req, res) => {
  let { bank_name, bank_time, bank_money, user_id, id, status } = req.body;
  bank_time = new Date(bank_time);
  if (id) {
    // 如果存在id，则执行更新操作
    const updateQuery = `
      UPDATE bank
      SET bank_name = ?, bank_time = ?, bank_money = ?, user_id = ?, status = ?
      WHERE id = ?
    `;
    connection.query(updateQuery, [bank_name, bank_time, bank_money, user_id, status, id], (error, results, fields) => {
      if (error) {
        console.error('Error updating bank record: ' + error);
        res.status(500).send('Error updating bank record');
      } else {
        res.status(200).send('Bank record updated successfully');
      }
    });
  } else {
    // 如果不存在id，则执行插入操作
    const insertQuery = `
      INSERT INTO bank (bank_name, bank_time, bank_money, user_id, status)
      VALUES (?, ?, ?, ?, ?)
    `;
    connection.query(insertQuery, [bank_name, bank_time, bank_money, user_id, status], (error, results, fields) => {
      if (error) {
        console.error('Error inserting bank record: ' + error);
        res.status(500).send('Error inserting bank record');
      } else {
        res.status(200).send('Bank record inserted successfully');
      }
    });
  }
});

// 获取作品列表

app.get('/work/list', verifyToken, (req, res) => {
  let { userId } = req.query

  let sql = 'SELECT * FROM work WHERE work.user_id =  ? ORDER BY id DESC'
  connection.query(sql, [userId], (err, results) => {
    if (err) {
      // 如果有错误，返回错误信息
      return res.status(500).send(createResponse(500, 'Internal Server Error'))
    }
    return res.status(200).json(createResponse(200, '', results))
  })
});


app.post('/work', verifyToken, (req, res) => {
  let { work_pic, title, time, exhibit, read, comment, user_id, id } = req.body;

  if (id) {
    // 如果存在id，则执行更新操作
    const updateQuery = `
    UPDATE work
    SET work_pic = ?, title = ?, time = ?, exhibit = ?, \`read\` = ?, comment = ?, user_id = ?
    WHERE id = ?
  `;
    connection.query(updateQuery, [work_pic, title, time, exhibit, read, comment, user_id, id], (error, results, fields) => {
      if (error) {
        console.error('Error updating work record: ' + error);
        res.status(500).send('Error updating work record');
      } else {
        res.status(200).send('Work record updated successfully');
      }
    });
  } else {
    // 如果不存在id，则执行插入操作
    const insertQuery = `
      INSERT INTO work (work_pic, title, time, exhibit, \`read\`, comment, user_id)
      VALUES (?, ?, ?, ?, ?, ?, ?)
    `;
    connection.query(insertQuery, [work_pic, title, time, exhibit, read, comment, user_id], (error, results, fields) => {
      if (error) {
        console.error('Error inserting work record: ' + error);
        res.status(500).send('Error inserting work record');
      } else {
        res.status(200).send('Work record inserted successfully');
      }
    });
  }
});
// 删除
app.delete('/work/:id', verifyToken, (req, res) => {
  const id = req.params.id;

  // 执行删除操作
  const deleteQuery = `
    DELETE FROM work
    WHERE id = ?
  `;
  connection.query(deleteQuery, [id], (error, results, fields) => {
    if (error) {
      console.error('Error deleting work record: ' + error);
      res.status(500).send('Error deleting work record');
    } else {
      res.status(200).send('Work record deleted successfully');
    }
  });
});
app.get('/notice/list', verifyToken, (req, res) => {
  let { userId } = req.query

  let sql = 'SELECT * FROM notice WHERE notice.user_id =  ?'
  connection.query(sql, [userId], (err, results) => {
    if (err) {
      // 如果有错误，返回错误信息
      return res.status(500).send(createResponse(500, 'Internal Server Error'))
    }
    return res.status(200).json(createResponse(200, '', results))
  })
});
app.post('/notice', verifyToken, (req, res) => {
  let { content, user_id, id } = req.body;

  if (id) {
    // 如果存在id，则执行更新操作
    const updateQuery = `
      UPDATE notice
      SET content = ?, user_id = ?
      WHERE id = ?
    `;
    connection.query(updateQuery, [content, user_id, id], (error, results, fields) => {
      if (error) {
        console.error('Error updating notice record: ' + error);
        res.status(500).send('Error updating notice record');
      } else {
        res.status(200).send('Notice record updated successfully');
      }
    });
  } else {
    // 如果不存在id，则执行插入操作
    const insertQuery = `
      INSERT INTO notice (content, user_id)
      VALUES (?, ?)
    `;
    connection.query(insertQuery, [content, user_id], (error, results, fields) => {
      if (error) {
        console.error('Error inserting notice record: ' + error);
        res.status(500).send('Error inserting notice record');
      } else {
        res.status(200).send('Notice record inserted successfully');
      }
    });
  }
});

// 删除
app.delete('/notice/:id', verifyToken, (req, res) => {
  const id = req.params.id;

  // 执行删除操作
  const deleteQuery = `
    DELETE FROM notice
    WHERE id = ?
  `;
  connection.query(deleteQuery, [id], (error, results, fields) => {
    if (error) {
      console.error('Error deleting work record: ' + error);
      res.status(500).send('Error deleting work record');
    } else {
      res.status(200).send('Work record deleted successfully');
    }
  });
});




app.get('/advanced/list', verifyToken, (req, res) => {
  let { userId } = req.query

  let sql = 'SELECT * FROM advanced WHERE advanced.user_id =  ?'
  connection.query(sql, [userId], (err, results) => {
    if (err) {
      // 如果有错误，返回错误信息
      return res.status(500).send(createResponse(500, 'Internal Server Error'))
    }
    return res.status(200).json(createResponse(200, '', results))
  })
});

app.post('/advanced', verifyToken, (req, res) => {
  let { pic, title, id, video_time, user_id } = req.body;

  if (id) {
    // 如果存在id，则执行更新操作
    const updateQuery = `
      UPDATE advanced
      SET pic = ?, title = ?, video_time = ?, user_id = ?
      WHERE id = ?
    `;
    connection.query(updateQuery, [pic, title, video_time, user_id, id], (error, results, fields) => {
      if (error) {
        console.error('Error updating advanced record: ' + error);
        res.status(500).send('Error updating advanced record');
      } else {
        res.status(200).send('Advanced record updated successfully');
      }
    });
  } else {
    // 如果不存在id，则执行插入操作
    const insertQuery = `
      INSERT INTO advanced (pic, title, video_time, user_id)
      VALUES (?, ?, ?, ?)
    `;
    connection.query(insertQuery, [pic, title, video_time, user_id], (error, results, fields) => {
      if (error) {
        console.error('Error inserting advanced record: ' + error);
        res.status(500).send('Error inserting advanced record');
      } else {
        res.status(200).send('Advanced record inserted successfully');
      }
    });
  }
});
// 删除
app.delete('/advanced/:id', verifyToken, (req, res) => {
  const id = req.params.id;
  // 执行删除操作
  const deleteQuery = `
    DELETE FROM advanced
    WHERE id = ?
  `;
  connection.query(deleteQuery, [id], (error, results, fields) => {
    if (error) {
      console.error('Error deleting work record: ' + error);
      res.status(500).send('Error deleting work record');
    } else {
      res.status(200).send('Work record deleted successfully');
    }
  });
});
app.get('/user/list', verifyToken, (req, res) => {
  let { userId } = req.query
  if (userId == 1 || userId == 2) {
    let sql = 'SELECT * FROM user'
    connection.query(sql, (err, results) => {
      if (err) {
        // 如果有错误，返回错误信息
        return res.status(500).send(createResponse(500, 'Internal Server Error'))
      }
      return res.status(200).json(createResponse(200, '', results))
    })
  } else {
    return res.status(500).send(createResponse(500, '没有权限'))
  }
});

app.post('/user', (req, res) => {
  let { phone, password, id } = req.body;






  if (id) {
    // 如果存在id，则执行更新操作

    connection.query('SELECT * FROM user WHERE phone = ? AND id != ?', [phone, id], function (error, results, fields) {
      if (error) {
        return res.status(500).send(createResponse(500, 'Internal Server Error'))
      }
      if (results.length > 0) {
        return res.send(createResponse(500, '用户已存在,请更换用户名'))
      }
      const updateQuery = `
      UPDATE user
      SET phone = ?, password = ?
      WHERE id = ?
    `;
      connection.query(updateQuery, [phone, password, id], (error, results, fields) => {
        if (error) {
          console.error('Error updating user record: ' + error);
          res.status(500).send('Error updating user record');
        } else {
          res.status(200).send('User record updated successfully');
        }
      });

    })
  } else {
    connection.query('SELECT * FROM user WHERE phone = ? ', [phone], function (error, results, fields) {
      if (error) {
        return res.status(500).send(createResponse(500, 'Internal Server Error'))
      }
      if (results.length > 0) {
        return res.send(createResponse(500, '用户已存在,请更换用户名'))
      }
      // 如果不存在id，则执行插入操作
      const insertQuery = `
        INSERT INTO user (phone, password)
        VALUES (?, ?)
      `;
      connection.query(insertQuery, [phone, password], (error, results, fields) => {
        if (error) {
          console.error('Error inserting user record: ' + error);
          res.status(500).send('Error inserting user record');
        } else {
          res.status(200).send('User record inserted successfully');
        }
      });
    })
  }





});
// 删除
app.delete('/user/:id', verifyToken, (req, res) => {
  const id = req.params.id;
  // 执行删除操作
  const deleteQuery = `
    DELETE FROM user
    WHERE id = ?
  `;
  connection.query(deleteQuery, [id], (error, results, fields) => {
    if (error) {
      console.error('Error deleting work record: ' + error);
      res.status(500).send('Error deleting work record');
    } else {
      res.status(200).send('Work record deleted successfully');
    }
  });
});



app.listen(3000, () => {
  console.log('Server started on port 3000')
})